--    Program: 		CAPPOIMP_PURCHASE_OPTION.sql
--    Description:  Import PO PURCHASE OPTION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_position_structure_id  number;
                    		                 		
   cursor cur_po_system_parameters_all is
      select * from CRST_PO_SYSTEM_PARAMETERS_ALL;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_po_system_parameters_all loop   
   	 l_err_msg_tmp := '';
   	 
   	 l_position_structure_id := null;
   	 if cur.SECURITY_POSITION_STRUCTURE_ID is not null then
   	   begin

== don't work
   	   	 select position_structure_id
         from   crst_per_position_structures
==

         where  position_structure_id = cur.SECURITY_POSITION_STRUCTURE_ID;
	   exception
	   	 when others then
	   	   l_err_msg_tmp := 'Position Hierarchy Structure '||cur.SECURITY_POSITION_STRUCTURE_ID||' not found'; 	
	   end;	   	
     end if;
     
     begin

==
     select   CREATION_DATE                   ,
          CREATED_BY                      ,
          SECURITY_POSITION_STRUCTURE_ID  ,
          ORG_ID
     from PO_SYSTEM_PARAMETERS_ALL		

==


            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.ACCRUED_CODE_COMBINATION_ID),
            l_position_structure_id             ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),

	 exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_PURCHASE_OPTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit

======================================================
select   CREATION_DATE                   ,
          CREATED_BY                      ,
          SECURITY_POSITION_STRUCTURE_ID  ,
          ORG_ID
from PO_SYSTEM_PARAMETERS_ALL		                            
          	   	
	  SELECT USER_ID
	  FROM FND_USER
	  
	  	SELECT	CODE_COMBINATION_ID		
			FROM	GL_CODE_COMBINATIONS
			
			
			--== FND_ORG_UNITS GUESS TO BE FOLLOWING ACCT  
			SELECT	ORGANIZATION_ID
			FROM HR_ALL_ORGANIZATION_UNITS
			
			SELECT *
			FROM HR_ALL_ORGANIZATION_UNITS_TL
======================================================


